clear
set more off
capture log using log/table1.log, replace

*Notes:
*1. we use updated currency data from 2017m3 to 2020m12 
*2. make sure only exEZ countries included. 
*Some EZ contries were included, almost all with euro currency: Andorra(AD), Kosova(XK) and San Marino(SM), and Montenegro (ME)
*previously used actual values in trade data rather than values in currency data. Now, they are close so use currency reported?
*missing currency transactions are dropped
*but keep full sample rather than just reg sample as this part is about stylized facts
*************************************************************************************************


*1. Exports 
*******************
*we use actual value rather than value_curr
*to infer share of currency use
*ex-Europe trade by diff for nonmissing currency
********************************************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	rename country cty_code
 	drop *I date
 	drop if missing(valueX)
 	drop if missing(currency)
    	rename valueX value
 	 	 	 	
 gen eurozone = inlist(cty_code, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
	replace eurozone =1 if inlist(cty_code, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(cty_code, "AD", "MC", "SM", "XK", "ME") 
	merge m:1 cty_code using ../rawdata/cty_name, keep(match master) nogen
	drop if eurozone==1
	
      	gen cat="EUR" if currency=="EUR"
	replace cat="USD" if currency=="USD"
	replace cat="other" if missing(cat)
		
	merge m:1 cty_code using ../rawdata/iso_cty_and_currency, keep(match master) nogen
	replace cty_name="User" if cty_code=="QW"
    	    	
    	gen type="NA" if currency~=currency_code & cat=="other"
    	
    	
	keep year month vat cty_code cty_name cncode value cat type
	
*split count shares and value shares by diff/nondif
	gen hs6=substr(cncode,1,6)
	merge m:1 hs6 using ../data/Rauch_hs6_concordance, keep(match master) 
	tab _merge
		
	gen dif=con=="n"
	gen count=1

save temp/Xcurr, replace

*share of exports that is differentiated
********************************************
	collapse (sum) count value, by(dif)
	egen totcount=sum(count)
	egen totvalue=sum(value)
	gen shvalue=value/totvalue
	gen shcount=count/totcount
	sum shvalue shcount if dif==1

use temp/Xcurr, clear
	
	collapse (sum) count value, by(cat dif)	
	reshape wide count value, i(cat) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep cat sh*
	order cat shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Exports") firstrow(variables) replace keepcellfmt

use temp/Xcurr, clear
	
	collapse (sum) count value, by(type dif)	
	reshape wide count value, i(type) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep type sh*
	order type shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Exports_other", replace) firstrow(variables) keepcellfmt


**********************************************************
*summary stats for imports
******************************************************
use ../../rawdata/update_feb21/currency_extraEU_2017_2020, clear
	rename country cty_code
 	drop *X date
 	drop if missing(valueI)
 	drop if missing(currency)
    	rename valueI value
 	 	 	 	
 gen eurozone = inlist(cty_code, "AT", "CY", "EE", "FI", "FR", "DE", "GR", "IE", "IT") 
	replace eurozone =1 if inlist(cty_code, "LV", "LT", "LU", "MT", "NL", "PT", "SK", "SI", "ES")
	replace eurozone = 1 if inlist(cty_code, "AD", "MC", "SM", "XK", "ME") 
	merge m:1 cty_code using ../rawdata/cty_name, keep(match master) nogen
	drop if eurozone==1
    	
    	gen cat="EUR" if currency=="EUR"
	replace cat="USD" if currency=="USD"
	replace cat="other" if missing(cat)
	
	merge m:1 cty_code using ../rawdata/iso_cty_and_currency, keep(match master) nogen
	replace cty_name="User" if cty_code=="QW"
	
	gen type="NA" if currency~=currency_code & cat=="other"
    	
	keep year month vat cty_code cty_name cncode value cat type
	
*split count shares and value shares by diff/nondif
	gen hs6=substr(cncode,1,6)
	merge m:1 hs6 using ../data/Rauch_hs6_concordance, keep(match master) 
	tab _merge
	gen dif=con=="n"
	gen count=1
save temp/Mcurr, replace

*share of imports that is differentiated
********************************************
	collapse (sum) count value, by(dif)
	egen totcount=sum(count)
	egen totvalue=sum(value)
	gen shvalue=value/totvalue
	gen shcount=count/totcount
	sum shvalue shcount if dif==1

use temp/Mcurr, clear
	
	collapse (sum) count value, by(cat dif)	
	reshape wide count value, i(cat) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep cat sh*
	order cat shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Imports", replace) firstrow(variables) keepcellfmt

use temp/Mcurr, clear
	
	collapse (sum) count value, by(type dif)	
	reshape wide count value, i(type) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep type sh*
	order type shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Imports_other", replace) firstrow(variables) keepcellfmt


*************************************************************************************************
****************************************************
*Redo above but dropping US
*****************************************************
*Exports
******************
*Share of US exports in 2018
************************************
use ../../rawdata/X_2018, clear
	gen value_US=value if land=="US"
	collapse (sum) value*
	gen USshare=value_US/value
	sum USshare

*share of US exports in crrency data
********************************************
use temp/Xcurr, clear
	gen value_US=value if cty_code=="US"
	collapse (sum) value*
	gen USshare=value_US/value
	sum USshare

*Table 1 without US
**************************
use temp/Xcurr, clear
	drop if cty_code=="US"
	collapse (sum) count value, by(cat dif)	
	reshape wide count value, i(cat) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep cat sh*
	order cat shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Exports_noUS", replace) firstrow(variables) keepcellfmt

*Imports
******************
*Share of US imports in 2018
************************************
use ../../rawdata/M_2018, clear
	gen value_US=value if land=="US"
	collapse (sum) value*
	gen USshare=value_US/value
	sum USshare

*share of US imports in crrency data
********************************************
use temp/Mcurr, clear
	gen value_US=value if cty_code=="US"
	collapse (sum) value*
	gen USshare=value_US/value
	sum USshare

*Table 1 without US
**************************
use temp/Mcurr, clear
	drop if cty_code=="US"
	collapse (sum) count value, by(cat dif)	
	reshape wide count value, i(cat) j(dif)
	
	gen count_all=count0+count1
	gen value_all=value0+value1
	egen totcount0=sum(count0)
	egen totcount1=sum(count1)
	egen totcount_all=sum(count_all)
	egen totvalue0=sum(value0)
	egen totvalue1=sum(value1)
	egen totvalue_all=sum(value_all)
	
	gen shcount_nodif=count0/totcount0
	gen shcount_dif=count1/totcount1
	gen shcount_all=count_all/totcount_all
	
	gen shvalue_dif=value1/totvalue1
	gen shvalue_nodif=value0/totvalue0
	gen shvalue_all=value_all/totvalue_all
	
	keep cat sh*
	order cat shcount_all shcount_dif shcount_nodif shvalue_all shvalue_dif shvalue_nodif

export excel ../results/sumstats/Table1, sheet("Imports_noUS", replace) firstrow(variables) keepcellfmt


****************************************
*share of trade with peg countries
**************************************
use temp/Xcurr, clear

    	merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
    	gen dum_usd= cat=="USD"
    	
    	gen value_usd=value*(dum_usd)
    	gen value_peg=value*(usd_peg==1)
    	
	collapse (sum) value* 
	gen shvalue_peg=value_peg/value 
	gen shvalue_usd=value_usd/value
	
	sum sh*
	local X_shvalue_peg: display %7.3f shvalue_peg
	local X_shvalue_usd: display %7.3f shvalue_usd

use temp/Xcurr, clear
	keep if year==2018
    	merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
    	gen dum_usd= cat=="USD"

	keep if con=="n"
    	
    	gen value_usd=value*(dum_usd)
    	gen value_peg=value*(usd_peg==1)
	collapse (sum) value* 
	gen shvalue_peg=value_peg/value 
	gen shvalue_usd=value_usd/value
	sum sh*
	local X_dif_shvalue_peg: display %7.3f shvalue_peg
	local X_dif_shvalue_usd: display %7.3f shvalue_usd

use temp/Mcurr, clear

    	merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
    	gen dum_usd= cat=="USD"
    	
    	gen value_usd=value*(dum_usd)
    	gen value_peg=value*(usd_peg==1)
    	
	collapse (sum) value* 
	gen shvalue_peg=value_peg/value 
	gen shvalue_usd=value_usd/value
	
	sum sh*
	local M_shvalue_peg: display %7.3f shvalue_peg
	local M_shvalue_usd: display %7.3f shvalue_usd
	
use temp/Mcurr, clear
	keep if year==2018
    	merge m:1 cty_code using ../data/usd_peg, keep(match master) nogen
    	gen dum_usd= cat=="USD"

	keep if con=="n"
    	
    	gen value_usd=value*(dum_usd)
    	gen value_peg=value*(usd_peg==1)
	collapse (sum) value* 
	gen shvalue_peg=value_peg/value 
	gen shvalue_usd=value_usd/value
	sum sh*
	local M_dif_shvalue_peg: display %7.3f shvalue_peg
	local M_dif_shvalue_usd: display %7.3f shvalue_usd


* Export peg shares stats to excel sheet 
putexcel set ../results/sumstats/descriptive_stats_pegs.xls, sheet(peg_shares) modify
	putexcel A1 = "Share of trade with peg countries"
	putexcel B3 = "Share with peg countries"
	putexcel C3 = "Share with USD countries"
	putexcel A4 = "Exports" 
	putexcel B4 = `X_shvalue_peg'
	putexcel C4 = `X_shvalue_usd'
	putexcel A5 = "Exports differentiated goods"
	putexcel B5 = `X_dif_shvalue_peg'
	putexcel C5 = `X_dif_shvalue_usd'
	putexcel A6 = "Imports"
	putexcel B6 = `M_shvalue_peg'
	putexcel C6 = `M_shvalue_usd'
	putexcel A7 = "Imports differentiated goods"
	putexcel B7 = `M_dif_shvalue_peg'
	putexcel C7 = `M_dif_shvalue_usd'
	
log close


